﻿-----------------------------------------------------------------------------------------------
-------------------------------------pca_tareas.alertas----------------------------------------
-----------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pca_tareas.fn_add_alerta(varchar,--empresa_id $1
						    varchar,--oficina_id $2
						    bigint,--sesion_id $3
						    varchar,--usuario_id $4
						    varchar,--mensaje $5
						    integer,--desencadenador_id $6
						    integer[],--meses $7
						    integer[] --dias $8
						    )RETURNS bigint AS
$$
BEGIN
	INSERT INTO pca_tareas.alertas
		(empresa_id,
		oficina_id,
		sesion_id,
		usuario_id,
		mensaje,
		desencadenador_id)
	VALUES
		($1,$2,$3,$4,$5,$6);

	RETURN pca_global.fn_pseudo_encrypt_bigint(currval('pca.seq_alerta_id')::integer);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_tareas.fn_update_alerta(varchar,--empresa_id $1
						       varchar,--oficina_id $2
						       bigint,--alerta_id $3
						       bigint,--sesion_id $4
						       varchar,--mensaje $5
						       integer,--desencadenador_id $6
						       integer[],--meses $7
						       integer[] --dias $8
						       )RETURNS void AS
$$
BEGIN
	UPDATE pca_tareas.alertas SET
		sesion_id = $4,
		mensaje = $5,
		desencadenador_id = $6,
		activo = $7
	WHERE
		empresa_id = $1 AND
		oficina_id = $2 AND
		alerta_id = $3;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_tareas.fn_del_alerta(varchar,--empresa_id $1
						    varchar,--oficina_id $2
						    bigint --alerta_id $3
						    )RETURNS void AS
$$
BEGIN
	DELETE FROM 
		pca_tareas.alertas 
	WHERE 
		empresa_id = $1 AND
		oficina_id = $2 AND
		alerta_id = $3;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_tareas.fn_get_list_alertas(integer,--start $1
							  integer,--limit $2
							  varchar,--empresa_id $3
							  varchar --oficina_id $4
							  )RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
BEGIN
	OPEN cursor_result FOR
		SELECT
			*
		FROM
			pca_tareas.vw_alertas
		WHERE
			empresa_id = $3 AND
			oficina_id = $4
		ORDER BY
			fecha_hora_sistema ASC LIMIT $2 OFFSET $1;
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		SELECT
			count(*)
		FROM
			pca_tareas.vw_alertas
		WHERE
			empresa_id = $3 AND
			oficina_id = $4;
	RETURN NEXT cursor_totalrows;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_tareas.fn_get_list_alertas_dia(integer,--start $1
							      integer,--limit $2
							      varchar,--empresa_id $3
							      varchar --oficina_id $4
							      )RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
BEGIN
	OPEN cursor_result FOR
		SELECT
			*
		FROM
			pca_tareas.vw_alertas_dia
		WHERE
			empresa_id = $3 AND
			oficina_id = $4
		ORDER BY
			fecha_hora_sistema ASC LIMIT $2 OFFSET $1;
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		SELECT
			count(*)
		FROM
			pca_tareas.vw_alertas_dia
		WHERE
			empresa_id = $3 AND
			oficina_id = $4;
	RETURN NEXT cursor_totalrows;
END;
$$
LANGUAGE plpgsql;